from flask import Flask , render_template,request, url_for, flash, redirect
from gevent import pywsgi
import pymysql

app = Flask(__name__)  #创建一个 flask 对象 名字就是 文件名
app.config['SECRET_KEY']='xu jiang 666'

#装饰器 @app


def get_db_connection():
    # 创建数据库链接到database.db 文件
    conn = pymysql.Connection(
        host="localhost",
        port=3306,
        user="root",
        passwd="123456",
        db="data",
        charset="utf8",
        cursorclass=pymysql.cursors.DictCursor
    )
    return conn

@app.route('/')   # 使用路由，给 index 函数定义一个路由，然后游览器通过http 请求得到相对应的数据
def index():
    #调用上面的函数 ，获取链接
    conn = get_db_connection()
    # 创建游标对象
    cursor = conn.cursor()
    # 执行sql语句
    cursor.execute('SELECT * FROM posts')
    # 获取游标结果查询所有数据，放到变量posts中
    posts = cursor.fetchall()
    cursor.close()
    conn.close()
    print(posts)
    #把查询的posts 传给网页
    return render_template('index.html', posts=posts)

def get_post(post_id):
    # 调用上面的函数 ，获取链接
    conn = get_db_connection()
    # 创建游标对象
    cursor = conn.cursor()
    # 执行sql语句
    cursor.execute(f'SELECT * FROM posts where id={post_id}')
    # 获取游标结果查询所有数据，放到变量posts中
    post = cursor.fetchone()
    cursor.close()
    conn.close()
    return post

@app.route('/posts/<int:post_id>')
def post(post_id):
    post = get_post(post_id)
    return render_template('post.html', post=post)


@app.route('/posts/new', methods=('GET', 'POST'))
def new():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('标题不能为空!')
        elif not content:
            flash('内容不能为空')
        else:
            conn = get_db_connection()
            # 创建游标对象
            cursor = conn.cursor()
            cursor.execute('INSERT INTO posts (title, content) VALUES (%s, %s)',(title,content))
            conn.commit()
            cursor.close()
            conn.close()
            return redirect(url_for('index'))

    return render_template('new.html')
@app.route('/posts/<int:id>/edit', methods=('GET', 'POST'))
def edit(id):
    post=get_post(id)
    if request.method=='POST':
        title=request.form['title']
        content = request.form['content']
        if not title:
            flash("title is must!")
        else:
            conn=get_db_connection()
            # 创建游标对象
            cursor = conn.cursor()
            cursor.execute('UPDATE posts SET title =%s, content =%s WHERE id =%s', (title, content, id))
            conn.commit()
            cursor.close()
            conn.close()
            return redirect(url_for('index'))
    return render_template('edit.html', post=post)

@app.route('/posts/<int:id>/delete', methods=('POST',))
def delete(id):
    post = get_post(id)
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(f'DELETE FROM posts WHERE id = {id}')
    conn.commit()
    cursor.close()
    conn.close()
    flash('"{}" 删除成功!'.format(post['title']))
    return redirect(url_for('index'))

@app.route('/about')
def about():
    return render_template('about.html')

server = pywsgi.WSGIServer(('127.0.0.1', 5001), app)
server.serve_forever()